﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Transactions;
using CSW.Framework.Common;
using CSW.Framework.Common.Binding;
using CSW.Framework.Common.Binding.Interfaces;
using CSW.Framework.Common.Binding.ValueTypes;
using CSW.Framework.Common.Data;
using CSW.Framework.Common.Utils.StringUtils;
using CSW.Framework.Linq.QueryModel;
using CSW.Framework.Linq.SchemaModel;

namespace CSW.Framework.Linq
{
    /// <summary>
    /// A LINQ enabled <see cref="BusinessObject"/>.
    /// </summary>
    public abstract class BusinessObjectLinq<T> : BusinessObjectLinqBase<T>
        where T : IDatabase, new()
    {
        #region <<< Public Methods >>>

        /// <summary>
        /// Tries to save the record to the database. It is this method's responsibility to <see cref="BindableObject.CheckAllRules(out string)"/> and set <see cref="BusinessObject.IsEditting"/> to false.
        /// </summary>
        /// <param name="errorMessage">The error message.</param>
        /// <returns>
        /// 	<c>true</c> if successful; otherwise, <c>false</c>.
        /// </returns>
        public override bool TrySave(out string errorMessage)
        {
            if (!CheckAllRules(out errorMessage))
                return false;

            using (TransactionScope scope = new TransactionScope())
            {
                if (IsNew)
                {
                    InsertQuery sql = GetInsertSql(this);
                    DbParameter[] p = sql.GetSqlParameters(this);

                    if (sql.HasIdentity)
                    {
                        int identityValue = m_Database.ExecuteIdentityInsert(sql.Sql, p);
                        SetIdentity(identityValue);
                    }
                    else
                    {
                        int rowCount = m_Database.ExecuteSql(sql.Sql, p);

                        if (rowCount != 1)
                            throw new Exception(string.Format("Insert would cause {0} rows to be affected. The change has been cancelled.", rowCount));
                    }
                }
                else
                {
                    UpdateQuery sql = GetUpdateSql(this);
                    DbParameter[] p = sql.GetSqlParameters(this);
                    int rowCount = m_Database.ExecuteSql(sql.Sql, p);

                    if (rowCount != 1)
                    {
                        if (rowCount == 0)
                            throw new Exception("Row has been modified or no longer exists. The change has been cancelled.");
                        else
                            throw new Exception(string.Format("Update would cause {0} rows to be affected. The change has been cancelled.", rowCount));
                    }
                }

                if (!TrySaveDetails(out errorMessage))
                    return false;

                scope.Complete();
            }

            RefreshOriginalValues(this);
            ResetState();
            PopulateInitialValues();
            SetIsNew(false);

            if (HasComputedColumns)
                Refresh();

            return true;
        }

        /// <summary>
        /// Refreshes this instance from the database.
        /// </summary>
        public override void Refresh()
        {
            CheckInitialized();

            if (IsNew)
                return;

            BeginLoading();
            try
            {
                Type tableType = GetType();
                Table table = GetTable(tableType);

                Query query = new Query(m_Schema, table);

                Dictionary<string, MethodInfo> get = GetPropertyGetterDictionary(tableType, table);

                DbConnection connection;
                if (m_Schema.Provider == "SQL Server")
                    connection = new SqlConnection(m_ConnectionString);
                else if (m_Schema.Provider == "SQL Server CE")
                    connection = new System.Data.SqlServerCe.SqlCeConnection(m_ConnectionString);
                else
                    throw new Exception("Unknown provider");

                DataRow dataRow;
                using (connection)
                {
                    string where = string.Empty;
                    int paramCount = 0;
                    List<DbParameter> sqlParams = new List<DbParameter>();
                    foreach (TableColumn column in table.Columns)
                    {
                        if (!column.IsKey)
                            continue;

                        if (!string.IsNullOrEmpty(where))
                            where += " AND ";

                        string parameterName = string.Format("@p{0}", paramCount);
                        where += string.Format("{0}.[{1}] = {2}", "T0", column.ColumnName, parameterName);
                        paramCount++;

                        TableProperty tableProperty = GetPrimitiveTableProperty(table, column);
                        string propertyName = tableProperty.PropertyName;
                        object keyValue = get[propertyName].Invoke(this, null);
                        Debug.Assert(keyValue != null);

                        DbParameter p = DataHelper.GetDbParameter(connection, parameterName, column.SqlDbType, keyValue);
                        //SqlParameter p = new SqlParameter(parameterName, keyValue);
                        sqlParams.Add(p);
                    }
                    Debug.Assert(sqlParams.Count != 0);

                    query.SetMode(QueryElementType.Where);
                    query.AddWhere(where);

                    string commandText = query.GetQuery();

                    connection.Open();
                    dataRow = DataHelper.GetDataRow(connection, commandText, sqlParams.ToArray());
                }

                LoadFromDataRow(this, query, dataRow);

                ResetState();

                PopulateInitialValues();

                foreach (IChildListProperty list in DetailLists)
                {
                    if (!list.IsInitialized)
                        continue;

                    list.Refresh();
                }
            }
            finally
            {
                EndLoading();
            }
        }


        #endregion <<< Public Methods >>>

        #region <<< Protected Properties/Methods >>>

        /// <summary>
        /// Adds a unique rule for the specified property.
        /// </summary>
        /// <typeparam name="TValue">The property type.</typeparam>
        /// <param name="property">The unique property.</param>
        protected void AddUniqueField<TValue>(Property<TValue> property)
        {
            CheckInitialized();

            AddUniqueField(new[] { property });
        }

        /// <summary>
        /// Adds a multi-column unique rule for the specified properties.
        /// </summary>
        /// <param name="propertyList">The unique property list.</param>
        protected void AddUniqueField(IEnumerable<ComplexProperty> propertyList)
        {
            CheckInitialized();

            Table table = GetTable();
            string[] uqPropertyNames = propertyList.Select(p => p.PropertyName).ToArray();
            string[] uqColumnNames = table.Properties.Where(p => uqPropertyNames.Contains(p.PropertyName)).Select(p => p.Column.ColumnName).ToArray();

            if (uqPropertyNames.Length != uqColumnNames.Length)
                throw new Exception();

            Dictionary<string, MethodInfo> propDict = GetPropertyGetterDictionary(GetType(), table);
            Dictionary<string, FieldInfoWrapper> fieldDict = GetFieldDictionary(GetType(), table);

            List<TableProperty> pkProperties = table.Properties.Where(p => p.IsPrimitiveType && p.Column != null && p.Column.IsKey).ToList();
            List<TableProperty> uqProperties = table.Properties.Where(p => p.IsPrimitiveType && p.Column != null && uqColumnNames.Contains(p.Column.ColumnName)).ToList();

            if (uqProperties.Count != uqColumnNames.Length)
                throw new Exception();

            if (pkProperties.Count != table.PrimaryKey.Length)
                throw new Exception();

            StringBuilder pkColumnList = new StringBuilder();
            foreach (TableProperty pkProp in pkProperties)
            {
                if (pkColumnList.Length != 0)
                    pkColumnList.Append(", ");

                pkColumnList.Append(pkProp.Column.ColumnName);
            }

            StringBuilder originalSql;
            if (m_Schema.Provider == "SQL Server")
                originalSql = new StringBuilder(string.Format("SELECT {2} FROM [{0}].[{1}] (nolock) T1 WHERE ", table.Schema, table.TableName, pkColumnList));
            else if (m_Schema.Provider == "SQL Server CE")
                originalSql = new StringBuilder(string.Format("SELECT {1} FROM [{0}] T1 WHERE ", table.TableName, pkColumnList));
            else
                throw new Exception("Unknown provider");

            CheckConstraintDelegate ccd = (errorMessage => CheckUniqueness(errorMessage, this, propDict, fieldDict, pkProperties, uqProperties, originalSql));

            // Add delegate check constraint to UQ properties
            foreach (ComplexProperty property in propertyList)
                AddCheckConstraint(property, ccd);
        }

        private static void CheckUniqueness(ErrorMessage errorMessage, IBusinessObject obj, IDictionary<string, MethodInfo> propDict, IDictionary<string, FieldInfoWrapper> fieldDict, IEnumerable<TableProperty> pkProperties, IEnumerable<TableProperty> uqProperties, StringBuilder originalSql)
        {
            if (obj.ObjectState == BindableObjectState.Pristine)
                return;

            StringBuilder sql = new StringBuilder(originalSql.ToString());

            // TODO: handle other providers
            DbConnection conn = m_Database.GetThreadConnection();
            List<DbParameter> param = new List<DbParameter>();

            // don't skip on new records, also values aren't loaded from db so exception will be thrown on field getter
            bool valuesAreSame = !obj.IsNew;

            int i = 0;
            foreach (TableProperty property in uqProperties)
            {
                if (i > 0)
                    sql.Append(" AND ");

                sql.AppendFormat(" T1.[{0}] ", property.Column.ColumnName);

                object value = propDict[property.PropertyName].Invoke(obj, null);

                if (valuesAreSame)
                {
                    object origValue = fieldDict[property.PropertyName].GetValue(obj);

                    if (value != origValue)
                        valuesAreSame = false;
                }

                if (value == null)
                {
                    sql.Append(" IS NULL ");
                }
                else
                {
                    string paramName = string.Format("@p{0}", param.Count);

                    sql.AppendFormat(" = {0} ", paramName);
                    DbParameter p = DataHelper.GetDbParameter(conn, paramName, property.Column.SqlDbType, value);

                    param.Add(p);
                }

                i++;
            }

            if (!obj.IsNew)
            {
                if (valuesAreSame)
                {
                    return;
                }

                sql.Append(" AND (");
                i = 0;
                foreach (TableProperty pk in pkProperties)
                {
                    if (i > 0)
                        sql.Append(" OR ");

                    string paramName = string.Format("@p{0}", param.Count);
                    sql.AppendFormat(" T1.[{0}] != {1} ", pk.Column.ColumnName, paramName);

                    object value = propDict[pk.PropertyName].Invoke(obj, null);
                    DbParameter p = DataHelper.GetDbParameter(conn, paramName, pk.Column.SqlDbType, value);

                    param.Add(p);

                    i++;
                }
                sql.Append(")");
            }

            DataTable dt = DataHelper.GetDataTable(conn, sql.ToString(), param.ToArray());

            if (dt.Rows.Count != 0)
            {
                string errMsg = string.Empty;
                i = 0;
                foreach (TableProperty uqProperty in uqProperties)
                {
                    if (i > 0)
                        errMsg += ", ";

                    errMsg += string.Format("{0} '{1}'", StringConverter.GetPrettyName(uqProperty.PropertyName), propDict[uqProperty.PropertyName].Invoke(obj, null));

                    i++;
                }

                errMsg = errMsg.Trim() + " already exists in ";

                DataRow dr = dt.Rows[0];
                i = 0;
                foreach (TableProperty pk in pkProperties)
                {
                    if (i > 0)
                        errMsg += ", ";

                    string pkColumnName = pk.Column.ColumnName;
                    errMsg += string.Format("{0} {1}", StringConverter.GetPrettyName(pkColumnName), dr[pkColumnName]);

                    i++;
                }
                errMsg += ".";

                errorMessage.Append(errMsg);
            }
        }

        #endregion <<< Protected Methods >>>
    }
}
